# -*- coding: utf-8 -*-
import json

import pymysql

from temp.log_util import logger
from temp.readyaml import yaml_r


class student_db:
    def connect(self):
        my_yaml = yaml_r()
        db_connect = pymysql.Connect(host=my_yaml.readyaml()['db_config']["host"],
                                     port=my_yaml.readyaml()["db_config"]["port"],
                                     user=my_yaml.readyaml()["db_config"]["user"],
                                     password=str(my_yaml.readyaml()["db_config"]["password"]),
                                     database=my_yaml.readyaml()["db_config"]["db_name"],
                                     charset="utf8")
        logger.info(f"读取数据库连接信息{my_yaml.readyaml()['db_config']}")
        return db_connect

    def add_stu_db(self, name: str, age: int, gender: str):
        mydb = student_db().connect()
        cursor = mydb.cursor()
        insert_sql = ''' insert into student_info(name,age,gender) values(%s, %s, %s) '''
        values = (name, age, gender)
        cursor.execute(insert_sql, values)
        mydb.commit()
        cursor.close()
        mydb.close()
        logger.info(f"{name}insert success")

    def modify_stu_db(self, id: int, name: str, age: int, gender: str):
        mydb = student_db().connect()
        cursor = mydb.cursor()
        modify_sql = ''' update student_info set name=%s,age=%s,gender=%s where id=%s'''
        values = (name, age, gender, id)
        cursor.execute(modify_sql, values)
        mydb.commit()
        cursor.close()
        mydb.close()

    def delete_stu_db(self, id: int):
        mydb = student_db().connect()
        cursor = mydb.cursor()
        delete_sql = ''' delete from student_info where id=%s '''
        values = (id)
        cursor.execute(delete_sql, values)
        mydb.commit()
        cursor.close()
        mydb.close()
        logger.info(f"id为{str(id)}学生在表中已删除")

    def query_stu_db(self, id: None):
        # 判断为空查询所有
        if id == 'None':
            mydb = student_db().connect()
            cursor = mydb.cursor()
            query_sql = ''' select * from student_info'''
            cursor.execute(query_sql)
            results = cursor.fetchall()
            rows = [dict(zip([column[0] for column in cursor.description], row)) for row in results]

            mydb.commit()
            cursor.close()
            mydb.close()
            logger.info(f"all student query success")
            return rows
        # 查询单个
        else:
            mydb = student_db().connect()
            cursor = mydb.cursor()
            query_sql = ''' select * from student_info where id=%s '''
            values = (id)
            cursor.execute(query_sql, values)
            results = cursor.fetchall()
            rows = [dict(zip([column[0] for column in cursor.description], row)) for row in results]
            mydb.commit()
            cursor.close()
            mydb.close()
            logger.info(f"id为{id}学生查询成功")
            return rows


if __name__ == '__main__':
    my_content = student_db()
    msg = my_content.query_stu_db(86)

    print(msg)
